<?php
// created by: alfin akhret
// files dependency:
// - region_cluster.js
// - cluster.php (controller)
// - mcluster.php (model)
// - f-cluster.php (view)
// - smooth.php (template)
class Mcluster extends CI_Model
{
	function __construct()
	{
		parent::__construct();
	}

	function get_user_territory_type_id($usergroup)
	{
		$this->db->select('territory_type_id');
		$query = $this->db->get_where('t_mtr_user_group', array('user_group_id' => $usergroup));
		return $query->result();
	}

	function get_territory($type, $username, $territory_id)
	{
		if($territory_id == '' && $type == 'cluster') //cari cluster_name dan cluster_id
		{
			// $this->db->select('territory_id, territory_name, parent_id');
			// $query = $this->db->get_where('t_mtr_territory', array('user_id' => $username));
			// return $query->result();
			$sql = "select f_get_cluster('$username')";
			$query = $this->db->query($sql);
			$territory_id ="";
			foreach($query->result() as $r)
			{
				$territory_id = $r->f_get_cluster;
			}
			$this->db->select('territory_id, territory_name, parent_id');
			$query = $this->db->get_where('t_mtr_territory', array('territory_id' => $territory_id));
			return $query->result();
		}
		else if($territory_id == '' && $type == 'region') //cari region_name dan region_id
		{
			$region_id = "";
			$sql = "select f_get_region('$username')";
			$query = $this->db->query($sql);
			foreach ($query->result() as $r) {
				$region_id = $r->f_get_region;
			}
			
			$this->db->select('territory_id, territory_name');
			$query = $this->db->get_where('t_mtr_territory', array('territory_id' => $region_id));
			return $query->result();
		}
		else if($territory_id != '' && $type == 'cluster') //utk mencari cluster yg regionya diketahui
		{
			// $this->db->select('territory_id, territory_name'); 
			// $query = $this->db->get_where('t_mtr_territory', array('parent_id' => $territory_id));
			// return $query->result();
			$sql="select territory_id as mvalue, CONCAT_WS(' ',split_part(upper(territory_name), ' ',1), CAST(split_part(territory_name, ' ',2) AS INTEGER)) AS mdisplay
			from t_mtr_territory
			where parent_id = ".$territory_id."
			order by split_part(territory_name, ' ',1), CAST(split_part(territory_name, ' ',2) AS INTEGER)";
			$query = $this->db->query($sql);
			return $query->result();
		}
		else if($territory_id != '' && $type == 'region') //untuk mencari region yg cluster nya diketahui
		{
			$this->db->select('territory_id, territory_name');
			$query = $this->db->get_where('t_mtr_territory', array('territory_id' => $territory_id));
			return $query->result();
		}
	}


	function get_regions($territory_type_id)
	{
		$this->db->select('territory_id, territory_name');
		$this->db->order_by('territory_name', 'asc');
		$query = $this->db->get_where('t_mtr_territory', array('territory_type_id' => $territory_type_id));
		return $query->result();

	}

	function get_clusters($territory_type_id, $parent_id)
	{
		$sql="select territory_id as mvalue, CONCAT_WS(' ',split_part(upper(territory_name), ' ',1), CAST(split_part(territory_name, ' ',2) AS INTEGER)) AS mdisplay
			from t_mtr_territory
			where parent_id = ".$parent_id." and territory_type_id = ".$territory_type_id."
			order by split_part(territory_name, ' ',1), CAST(split_part(territory_name, ' ',2) AS INTEGER)";
		$query = $this->db->query($sql);
		return $query->result();
	}


	function get_cluster($territory_id)
	{
		$this->db->select('territory_id, territory_name');
		$query = $this->db->get_where('t_mtr_territory', array('territory_id' => $territory_id));
		return $query->result();
	}

	function get_cluster_data($territory_id)
	{
		$this->db->select('territory_name,user_id,parent_id');
		$query = $this->db->get_where('t_mtr_territory', array('territory_id' => $territory_id));
		return $query->result_array();
	}

	function insert_session_cluster($user_id, $territory_id)
	{
		$this->db->where('user_id', $user_id);
		$this->db->delete('t_session_territory'); 

		$data = array(
		   'user_id' => $user_id ,
		   'territory_id' => $territory_id
		);

		$query = $this->db->insert('t_session_territory', $data);

		$cluster_name="";
		$cluster = $this->get_cluster($territory_id);
		foreach ($cluster as $c) {
			$cluster_name = $c->territory_name;
		}
	}
	
	function clean_table()
	{
		if($this->session->userdata('username') != '')
		{
			$this->db->where('user_id', $this->session->userdata('username'));
			$this->db->delete('t_session_territory');
		}
	}
}